﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;

namespace RockCompany.Common.Lib
{
    public class DBConnector
    {
        #region
        public const string CONFIG_PATH = "..\\Common\\Config\\ConnectionConfig.txt";
        public const string SOLUTION_DIR = "..\\..\\..\\..";
        #endregion

        #region Variables
        private SqlConnection sqlConn;
        private SqlCommand sqlCmd;
        #endregion

        #region Constructor
        public DBConnector()
        {
            sqlConn = new SqlConnection();
            sqlCmd = new SqlCommand();
        }
        #endregion

        #region Methods
        public bool connect()
        {
            try
            {
                //string path = Path.GetFullPath(Application.StartupPath + CONFIG_PATH);
                string path = Path.GetFullPath(Application.StartupPath + SOLUTION_DIR + CONFIG_PATH);

                StreamReader streamRd = new StreamReader(path);
                sqlConn.ConnectionString = streamRd.ReadLine();
                sqlConn.Open();
            }
            catch(Exception ex)
            {
                Logger.addLog(Logger.ERROR + "\nDB connection : =================================" + ex.Message);
            }
            return sqlConn.State == ConnectionState.Open;
        }

        public bool close()
        {
            try
            {
                sqlConn.Close();
            }
            catch (Exception ex)
            {
                Logger.addLog(Logger.ERROR + "\nDB close : =================================" + ex.Message);
            }
            return sqlConn.State == ConnectionState.Closed;
        }

        public bool isConnected()
        {
            return sqlConn.State == ConnectionState.Open;
        }

        public ConnectionState getCurrentState() { return sqlConn.State; }


        public DataTable selectData(string sSql)
        {
            DataTable dataTbl = new DataTable();
            try
            {
                sqlCmd.CommandText = sSql;
                sqlCmd.Connection = sqlConn;
                SqlDataAdapter sqlDataAd = new SqlDataAdapter(sqlCmd);
                
                sqlDataAd.Fill(dataTbl);
            }
            catch (Exception ex)
            {
                Logger.addLog(Logger.ERROR + "\nSelect data with sql : =================================\n" + sSql + "\n" + ex.Message);
            }
            return dataTbl;
        }

        public DataTable selectData(SqlCommand pSqlCmd)
        {
            DataTable dataTbl = new DataTable();
            try
            {
                pSqlCmd.Connection = sqlConn;
                if (sqlConn.State == ConnectionState.Closed)
                    connect();
                SqlDataAdapter sqlDataAd = new SqlDataAdapter(sqlCmd);

                sqlDataAd.Fill(dataTbl);
            }
            catch (Exception ex)
            {
                Logger.addLog(Logger.ERROR + "\nSelect data with command : =================================\n" + pSqlCmd.CommandText + "\n" + ex.Message);
            }
            return dataTbl;
        }

        public int excuteNonQuery(string sSql)
        {
            int nRes = 0;
            try
            {
                sqlCmd.CommandText = sSql;
                sqlCmd.Connection = sqlConn;
                nRes = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logger.addLog(Logger.ERROR + "\nExcuteNonQuery with sql : =================================\n" + sSql + "\n" + ex.Message);
            }
            return nRes;
        }

        public int excuteNonQuery(SqlCommand pSqlCmd)
        {
            int nRes = 0;
            try
            {
                pSqlCmd.Connection = sqlConn;
                nRes = pSqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Logger.addLog(Logger.ERROR + "\nExcuteNonQuery with command : =================================\n" + pSqlCmd.CommandText + "\n" + ex.Message);
            }
            return nRes;
        }

        public void addParameters(string sKey, object oValue)
        {
            try
            {
                sqlCmd.Parameters.AddWithValue(sKey, oValue);
            }
            catch (Exception ex)
            {
                Logger.addLog(Logger.ERROR + "\nAddParameters : =================================" + ex.Message);
            }
        }

        public void removeParameters()
        {
            try
            {
                sqlCmd.Parameters.Clear();
            }
            catch (Exception ex)
            {
                Logger.addLog(Logger.ERROR + "\nRemoveParameters : =================================" + ex.Message);
            }
        }

        public void setProcedureMode() { sqlCmd.CommandType = CommandType.StoredProcedure; }
        public void setTextMode() { sqlCmd.CommandType = CommandType.Text; }
        #endregion
    }
}
